﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Transactions;

namespace Core.DMS.Providers
{
    public class SqlDocumentProvider : DocumentProvider
    {
        string connectionString = "";

        public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
        {
            if (config["connectionString"] != null)
                connectionString = config["connectionString"];
            if (config["connectionStringName"] != null)
                connectionString = ConfigurationManager.ConnectionStrings[config["connectionStringName"]].ConnectionString;

            if (connectionString == null)
                throw new ConfigurationErrorsException("Failed to locate a connection string for the content store.");

            base.Initialize(name, config);
        }

        public override Document GetDocument(int DocumentID)
        {
            Document result = null;

            using (SqlCommand com = new SqlCommand("GetDocument", new SqlConnection(this.connectionString)))
            {
                com.CommandType = System.Data.CommandType.StoredProcedure;
                com.Parameters.AddWithValue("@ID", DocumentID);
                com.Connection.Open();
                SqlDataReader pageInfoReader = com.ExecuteReader();
                if (pageInfoReader.Read())
                    result = new Document(pageInfoReader);
                com.Connection.Close();
            }

            return result;
        }

        public override int SaveDocument(Document doc, Stream contentStream)
        {
            int result = -1;

            using (TransactionScope scope = new TransactionScope())
            {
                using (SqlCommand com = new SqlCommand("SaveDocument", new SqlConnection(this.connectionString)))
                {
                    com.CommandType = System.Data.CommandType.StoredProcedure;
                    com.Parameters.AddWithValue("@ID", doc.ID);
                    com.Parameters.AddWithValue("@FolderID", doc.Parent);
                    com.Parameters.AddWithValue("@Title", doc.Title);
                    com.Parameters.AddWithValue("@Filename", doc.Filename);
                    com.Parameters.AddWithValue("@Description", doc.Description);

                    com.Connection.Open();
                    if (contentStream != null)
                    {
                        int bytesRead = 0;
                        int bytesSaved = 0;
                        int dataID = -1;
                        int chunkSize = 0;

                        SqlCommand com2 = new SqlCommand("SaveDocumentContent", com.Connection);
                        com2.CommandType = System.Data.CommandType.StoredProcedure;
                        byte[] buffer = new byte[1024];
                        chunkSize = contentStream.Read(buffer, 0, buffer.Length);

                        while (chunkSize == buffer.Length)
                        {
                            bytesRead += chunkSize;
                            com2.Parameters.Clear();
                            com2.Parameters.AddWithValue("@ID", dataID);
                            com2.Parameters.AddWithValue("@Data", buffer);
                            com2.Parameters.AddWithValue("@Offset", bytesSaved);
                            com2.Parameters.AddWithValue("@DataLength", chunkSize);

                            if (dataID == -1)
                            {
                                object dataResult = com2.ExecuteScalar();
                                if (dataResult != DBNull.Value)
                                    dataID = int.Parse(dataResult.ToString());
                            }
                            else
                                com2.ExecuteScalar();

                            bytesSaved = bytesRead;
                            chunkSize = contentStream.Read(buffer, 0, buffer.Length);
                        }
                        if (dataID == -1)
                            throw new Exception("Problem saving file to the database.");

                        com.Parameters.AddWithValue("@DataID", dataID);
                        com.Parameters.AddWithValue("@Size", bytesSaved);
                    }
                    object queryResult = com.ExecuteScalar();

                    if (queryResult != DBNull.Value)
                        result = int.Parse(queryResult.ToString());

                    com.Connection.Close();
                }

                if (result > -1)
                    scope.Complete();
            }

            return result;
        }

        public override void DeleteDocument(int DocumentID)
        {
            if (DocumentID != -1)
            {
                using (SqlCommand com = new SqlCommand("DeleteDocument", new SqlConnection(this.connectionString)))
                {
                    com.CommandType = System.Data.CommandType.StoredProcedure;
                    com.Parameters.AddWithValue("@ID", DocumentID);
                    com.Connection.Open();
                    com.ExecuteNonQuery();
                    com.Connection.Close();
                }
            }
        }

        public override void GetDocumentContent(int DocumentID, Stream outputStream)
        {
            using (SqlCommand com = new SqlCommand("GetDocumentContent", new SqlConnection(this.connectionString)))
            {
                com.CommandType = System.Data.CommandType.StoredProcedure;
                com.Parameters.AddWithValue("@ID", DocumentID);
                com.Connection.Open();
                SqlDataReader fileReader = com.ExecuteReader(CommandBehavior.SequentialAccess);

                // The BLOB byte[] buffer to be filled by GetBytes.
                byte[] buffer = new byte[1024];
                // The bytes returned from GetBytes.
                long retval;
                // The starting position in the BLOB output.
                long startIndex = 0;

                while (fileReader.Read())
                {
                    // Reset the starting byte for the new BLOB.
                    startIndex = 0;

                    // Read bytes into outByte[] and retain the number of bytes returned.
                    retval = fileReader.GetBytes(0, startIndex, buffer, 0, buffer.Length);

                    // Continue while there are bytes beyond the size of the buffer.
                    while (retval == buffer.Length)
                    {
                        outputStream.Write(buffer, 0, buffer.Length);
                        // Reposition start index to end of last buffer and fill buffer.
                        startIndex += buffer.Length;
                        retval = fileReader.GetBytes(0, startIndex, buffer, 0, buffer.Length);
                    }

                    // Write the remaining buffer.
                    outputStream.Write(buffer, 0, (int)retval);
                    outputStream.Flush();
                }

                fileReader.Close();
                com.Connection.Close();
            }
        }

        public override int SaveFolder(Folder folder)
        {
            int result = -1;

            using (SqlCommand com = new SqlCommand("SaveFolder", new SqlConnection(this.connectionString)))
            {
                com.CommandType = System.Data.CommandType.StoredProcedure;
                com.Parameters.AddWithValue("@ID", folder.ID);
                com.Parameters.AddWithValue("@Parent", folder.Parent);
                com.Parameters.AddWithValue("@Title", folder.Title);
                com.Connection.Open();
                result = (int)com.ExecuteScalar();
                com.Connection.Close();
            }

            return result;
        }

        public override Folder GetFolder(int FolderID)
        {
            Folder result = null;
            if (FolderID != -1)
            {
                using (SqlCommand com = new SqlCommand("GetFolder", new SqlConnection(this.connectionString)))
                {
                    com.CommandType = System.Data.CommandType.StoredProcedure;
                    com.Parameters.AddWithValue("@ID", FolderID);
                    com.Connection.Open();
                    SqlDataReader folderReader = com.ExecuteReader();
                    if (folderReader.Read())
                        result = new Folder(folderReader);
                    com.Connection.Close();
                }
            }
            return result;
        }

        public override List<FileSystemObject> GetFolderContents(int FolderID)
        {
            List<FileSystemObject> result = new List<FileSystemObject>();

            using (SqlCommand com = new SqlCommand("GetFolderContents", new SqlConnection(this.connectionString)))
            {
                com.CommandType = System.Data.CommandType.StoredProcedure;
                com.Parameters.AddWithValue("@ID", FolderID);
                com.Connection.Open();
                SqlDataReader contentReader = com.ExecuteReader();
                while (contentReader.Read())
                {
                    if (contentReader["Type"].ToString() == "Folder")
                        result.Add(new Folder(contentReader));
                    else if(contentReader["Type"].ToString() == "Document")
                        result.Add(new Document(contentReader));
                }
                com.Connection.Close();
            }

            return result;
        }

        public override void DeleteFolder(int FolderID)
        {
            using (SqlCommand com = new SqlCommand("DeleteFolder", new SqlConnection(this.connectionString)))
            {
                com.CommandType = System.Data.CommandType.StoredProcedure;
                com.Parameters.AddWithValue("@ID", FolderID);
                com.Connection.Open();
                com.ExecuteNonQuery();
                com.Connection.Close();
            }
        }
    }
}
